package kr.or.kead.service;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.or.kead.domain.Depart;
import kr.or.kead.domain.InfoStudent;
import kr.or.kead.utils.MySqlCon;

public class Daodepart implements DaoTable {

	@Override
	public int insertDao(Object obj) {
		Connection con = MySqlCon.getConn();
		Depart depart = (Depart) obj;
		String sql = "insert into depart values(?,?,?,?)";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, depart.getCode());
			pstmt.setString(2, depart.getName());
			pstmt.setInt(3, depart.getProt());
			pstmt.setString(4, depart.getTel());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally {
			try { pstmt.close(); con.close(); } 
			catch (SQLException e) { e.printStackTrace(); }
		}
		return 0;
	}

	@Override
	public int updateDao(Object obj) {
		Connection con = MySqlCon.getConn();
		Depart depart = (Depart) obj;
		String sql = "update depart set code=?, name=?, prof=?, tel=? where code=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, depart.getCode());
			pstmt.setString(2, depart.getName());
			pstmt.setInt(3, depart.getProt());
			pstmt.setString(4, depart.getTel());
			pstmt.setInt(5, depart.getCode());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally {
			try { pstmt.close(); con.close(); } 
			catch (SQLException e) { e.printStackTrace(); }
		}
		return 0;
	}

	@Override
	public int deleteDao(int regNo) {
		Connection con = MySqlCon.getConn();
		String sql = "delete from depart where code=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, regNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally {
			try { pstmt.close(); con.close(); } 
			catch (SQLException e) { e.printStackTrace(); }
		}
		return 0;
	}

	@Override
	public ArrayList<Object> selectDao() {
		Connection con = MySqlCon.getConn();
		String sql = "select code,name,prof,tel from depart";
		ArrayList<Object> departs = new ArrayList<>();
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				departs.add(new Depart(
								rs.getInt(1), 
								rs.getString(2), 
								rs.getInt(3), 
								rs.getString(4))
				);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try { pstmt.close(); con.close(); } 
			catch (SQLException e) { e.printStackTrace(); }
		}
		return departs;
	}

	@Override
	public Object selectTableById(int stdId) {
		Connection con = MySqlCon.getConn();
		String sql = "select code,name,prof,tel from depart where code=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, stdId);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				return new Depart(
						rs.getInt(1), 
						rs.getString(2), 
						rs.getInt(3), 
						rs.getString(4));
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try { pstmt.close(); con.close(); } 
			catch (SQLException e) { e.printStackTrace(); }
		}
		return null;
	}

	public int selectCodeDepartByName(String name){
		Connection con = MySqlCon.getConn();
		String sql = "select code from depart where name=?";
		PreparedStatement pstmt=null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, name);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				return rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
		return -1;
	}
	
	public int selectMaxCode(){
		Connection con = MySqlCon.getConn();
		String sql = "select max(code) from depart";
		PreparedStatement pstmt=null;
		try {
			pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				return rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
		return -1;
	}

	@Override
	public ArrayList<String> selectTableAllList() {
		Connection con = MySqlCon.getConn();
		String sql = "select code, name from depart";
		ArrayList<String> departs = new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) departs.add(rs.getInt(1)+" : "+rs.getString(2));
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs.close();
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return departs;
	}

	@Override
	public Object selectTableByEmail(String email) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int updataTableByEmail(String pass, String email) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public ArrayList<String> selectTableAllNames() {
		Connection con = MySqlCon.getConn();
		String sql = "select name from depart";
		ArrayList<String> departs = new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) departs.add(rs.getString(1));
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs.close();
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return departs;
	}
}
